package com.yzyz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.yzyz.bean.BaseBean;
import com.yzyz.bean.route.RouteGroupBean;
import com.yzyz.dao.RouteGroupDao;

/**
 * 
 * @author David Sun
 * @Date   2010-4-6
 *
 */
public class RouteGroupDaoImpl implements RouteGroupDao {
		
	private JdbcTemplate jdbcTemplate;
	
	@SuppressWarnings("unchecked")
	@Override
	public List<RouteGroupBean> paginate(int start, int limit) {
		String sql = "select * from routegroup limit " + start + " , " + limit;
		return this.jdbcTemplate.query(sql, new RouteGroupRowMapper());
	}

	@Override
	public int delete(int id) {
		String sql = "delete from routegroup where id=" + id;
		return this.jdbcTemplate.update(sql);
	}

	@Override
	public Object get(int id) {
		String sql = "select * from routegroup where id=" + id;
		return (Object)this.jdbcTemplate.queryForObject(sql, new RouteGroupRowMapper());
	}

	@Override
	public int getCount(int ownerId) {
		String sql = "select count(*) from routegroup";
		return this.jdbcTemplate.queryForInt(sql);
	}

	@Override
	public int save(final Object object) {
		
		final String sql = "insert into routegroup(name) values(?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator(){

			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				java.sql.PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, ((RouteGroupBean)object).getName());
				return ps;
			}}, keyHolder);
		return keyHolder.getKey().intValue();
	}

	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;

	}

	@Override
	public void update(final Object object) {
		String sql = "update routegroup set name=? where id=?";
		this.jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, ((RouteGroupBean)object).getName());
				ps.setInt(2, ((RouteGroupBean)object).getId());
				
			}});
	}

	@Override
	public int updateAvailable(int id, boolean available) {
		// TODO Auto-generated method stub
		return 0;
	}
	
	private class  RouteGroupRowMapper implements RowMapper{

		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			RouteGroupBean group = new RouteGroupBean();
			group.setId(rs.getInt("id"));
			group.setName(rs.getString("name"));
			return group;
		}
		
	}

	@Override
	public int getCount() {
		String sql = "select count(*) from routegroup";
		return this.jdbcTemplate.queryForInt(sql);
	}

}
